<?php
class PlanoDAO extends GenericDAO  {

	function __construct() {
		$this->tabela["alteracao"] = array(	"id" 	=> 144,
											"nome" 	=> strtolower(substr(get_class(), 0, -3)));
	
		$this->tabela["consulta"]  = array(	"id" 	=> 149,
											"nome" 	=> "vw_" . strtolower(substr(get_class(), 0, -3)));
	}
	
	public function consultarPK($valor) {
	    $sql = "where id = :id";
	    $params['id'] = $valor;
	    return $this->consultar($sql, $params);
	}
	
	public function consultarCodigo($valor) {
		$sql = "where codigo like :codigo";
		$params['codigo'] = $valor;
	    return $this->consultar($sql, $params);
	}
	
	public function consultarCodigoDesc($plano, $dc = "") {
		$where = 'where codigo like :codigo or descricao like :desc';
		$params['codigo'] = "$plano%";
		$params['desc'] = "%$plano%";
		if ($dc != "") {
			$where .= " and dc = :dc";
			$params['dc'] = $dc;
		}
		return $this->consultar($where, $params);
	}

	public function consultarCodigoDescQuadra($plano, $q_id) {
		$where = 'where (codigo like :codigo or descricao like :desc) and codigo like (select concat(plano_codigo, "%") from vw_quadra where id = :q_id)';
		$params['codigo'] = "$plano%";
		$params['desc'] = "%$plano%";
		$params['q_id'] = $q_id;
		return $this->consultar($where, $params);
	}

	public function inserir($registro, $finalizarTransacao = true) {
		$res = null;
		if ($this->inserirRegistro($registro, $finalizarTransacao)) {
			$res = $registro;
			$res['id'] = $this->ultimoId();
		}
		return $res;
	}
	
	public function excluir($id, $finalizarTransacao = true) {
		$pk['id'] = $id;
		return $this->excluirRegistro($pk, $finalizarTransacao);	
	}
	
	public function alterar($registro, $finalizarTransacao = true) {
		$pk['id'] 		= $registro['id'];
		unset($registro['id']);
		return $this->alterarRegistro($registro, $pk, $finalizarTransacao);
	}
	
	public function consultarCentroCustos($dt_ini, $dt_fim) {
		$sql = "	select 	'' conta,
							'Saldo em $dt_ini' descricao,
							'' vr1,
							'' vr2,
							'' vr3
					union all
					select 	'' conta,
							s1.conta_nome descricao,
							'' vr1,
							s1.saldo_atual vr2,
							'' vr3
					from 	vw_saldo s1 inner join (select 	conta_id,
															max(id) id
													from 	vw_saldo
													where 	dt_data <= :dt_ini
													group by conta_id) s2
												on s1.id = s2.id
					union all
					select 	'' conta,
							'Sub-Total' descricao,
							'' vr1,
							'' vr2,
							replace(replace(replace(format(sum(s1.vr_saldo_atual), 2), '.', '@'), ',', '.'), '@', ',') vr3
					from 	vw_saldo s1 inner join (select 	conta_id,
															max(id) id
													from 	vw_saldo
													where 	dt_data <= :dt_ini
													group by conta_id) s2
												on s1.id = s2.id
					union all
					select 	'' conta,
							'---------------------------------------------------------' descricao,
							'' vr1,
							'' vr2,
							'' vr3
					union all
					select	plano_codigo conta,
							plano_descricao descricao,
							'' vr1,
							'' vr2,
							case plano_dc
								when 'D' then sum(valor) * -1
								else sum(valor)
							end vr3
					from	vw_movimento
					group by plano_codigo,
							plano_descricao,
							plano_dc
					union all
					select 	'' conta,
							'---------------------------------------------------------' descricao,
							'' vr1,
							'' vr2,
							'' vr3
					union all
					select 	'' conta,
							'Saldo em $dt_fim' descricao,
							'' vr1,
							'' vr2,
							'' vr3
					union all
					select 	'' conta,
							s1.conta_nome descricao,
							'' vr1,
							s1.saldo_atual vr2,
							'' vr3
					from 	vw_saldo s1 inner join (select 	conta_id,
															max(id) id
													from 	vw_saldo
													where 	dt_data <= :dt_fim
													group by conta_id) s2
												on s1.id = s2.id
					union all
					select 	'' conta,
							'Sub-Total' descricao,
							'' vr1,
							'' vr2,
							replace(replace(replace(format(sum(s1.vr_saldo_atual), 2), '.', '@'), ',', '.'), '@', ',') vr3
					from 	vw_saldo s1 inner join (select 	conta_id,
															max(id) id
													from 	vw_saldo
													where 	dt_data <= :dt_fim
													group by conta_id) s2
												on s1.id = s2.id";
		
		$data = explode("/", $dt_ini);
		$params['dt_ini'] = $data[2] . "-" . $data[1] . "-" . $data[0];
		
		$data = explode("/", $dt_fim);
		$params['dt_fim'] = $data[2] . "-" . $data[1] . "-" . $data[0];
		
		return $this->execQuery($sql, $params);
	}
}
?>